package com.movile.checkhoras.android.dao;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.movile.checkhoras.android.vo.CheckHora;
import com.movile.checkhoras.android.vo.MesAno;

/**
 * @author felipe.silvestre
 */
public class TableCheckHorasHelper {

    private static final String DATABASE_NAME = "checkhoras.db";
    private static final int DATABASE_VERSION = 1;
    public static final String TABLE_NAME = "horas";

    public static final int CHECKIN = 0;
    public static final int CHECKOUT = 1;

    public static final String COL_ID = "id";
    public static final String COL_DIA = "dia";
    public static final String COL_MES = "mes";
    public static final String COL_ANO = "ano";
    public static final String COL_DATASTRING = "datastring";
    public static final String COL_MILISEGUNDOS = "milisegundos";
    //public static final String COL_DATAHORA = "datahora";
    public static final String COL_HORA = "hora";
    public static final String COL_MINUTO = "minuto";
    public static final String COL_TIPO = "tipo";

    private Context context;
    private SQLiteDatabase db;

    private SQLiteStatement insertStmt;
    private static final String INSERT = "insert into " + TABLE_NAME + "(name) values (?)";

    public TableCheckHorasHelper(Context context) {
        this.context = context;
        OpenHelper openHelper = new OpenHelper(this.context);
        
        this.db = openHelper.getWritableDatabase();
        //openHelper.onCreate(db);
       // this.insertStmt = this.db.compileStatement(INSERT);
    }

    
    public void insert(CheckHora check) {

       // clearDatabase();
        ContentValues values = new ContentValues();
        //values.put(COL_ID, nextID());
        values.put(COL_ANO, check.getAno());
        values.put(COL_DATASTRING, check.getDataString());
        values.put(COL_DIA, check.getDia());
        values.put(COL_HORA, check.getHora());
        values.put(COL_MES, check.getMes());
        values.put(COL_MILISEGUNDOS, String.valueOf(check.getMilisegundos()));
        values.put(COL_MINUTO, check.getMinuto());

        if (check.isCheckIn()) {
            values.put(COL_TIPO, CHECKIN);
        } else {
            values.put(COL_TIPO, CHECKOUT);
        }

        db.insert(TABLE_NAME, null, values);
    }

    public void deleteAll() {
        this.db.delete(TABLE_NAME, null, null);
    }
    
    public void deleteCheck(int id){
        this.db.delete(TABLE_NAME, COL_ID + "=" + id, null);
    }
    
    public void deletePeriodo(int mes, int ano){
        this.db.delete(TABLE_NAME, COL_MES + "=" + mes + " and " + COL_ANO + "=" + ano, null);
    }

    public boolean IsCheckInLast() {
        Cursor cursor = this.db.query(TABLE_NAME, new String[] {"id,dia,mes,ano,datastring,milisegundos,hora,minuto,tipo"}, null, null, null, null,
            "dia desc, hora desc, minuto desc, id desc");
        if (cursor.moveToFirst()) {

            if (cursor.getInt(8) == CHECKIN) {
                return true;
            } else {
                return false;
            }
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return false;
    }
    
    private void clearDatabase(){
        this.db.delete(TABLE_NAME, null, null);
    }

    @SuppressWarnings("unused")
    private int nextID() {

        Cursor cursor = this.db.query(TABLE_NAME, new String[] {"id"}, null, null, null, null, "id desc");
        if (cursor.moveToFirst()) {

            return cursor.getInt(0);             
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return 1;

    }

    public ArrayList<MesAno> selectMouths() {
        ArrayList<MesAno> list = new ArrayList<MesAno>();
        Cursor cursor = this.db.query(TABLE_NAME, new String[] {"id,dia,mes,ano,datastring,milisegundos,hora,minuto,tipo"}, null, null, null, null,
            "id desc");
        if (cursor.moveToFirst()) {

            MesAno mesAno;
            do {

                mesAno = new MesAno(context);
                mesAno.setMes(cursor.getInt(2));
                mesAno.setAno(cursor.getInt(3));

                if (!list.contains(mesAno)) {
                    list.add(mesAno);
                }

            } while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    public ArrayList<CheckHora> selectAll(MesAno mesAno) {

        final String WHERE_CLAUSE = "ano = " + mesAno.getAno() + " and mes = " + mesAno.getMes();

        ArrayList<CheckHora> list = new ArrayList<CheckHora>();
        Cursor cursor = this.db.query(TABLE_NAME, new String[] {"id,dia,mes,ano,datastring,milisegundos,hora,minuto,tipo"}, WHERE_CLAUSE, null, null,
            null, "dia desc, hora desc, minuto desc, id desc");
        if (cursor.moveToFirst()) {

            CheckHora check;
            do {

                check = new CheckHora();
                check.setId(cursor.getInt(0));
                check.setDia(cursor.getInt(1));
                check.setMes(cursor.getInt(2));
                check.setAno(cursor.getInt(3));
                check.setDataString(cursor.getString(4));
                check.setMilisegundos(Long.parseLong(cursor.getString(5)));
                // check.setDatahHora(datahHora)
                check.setHora(cursor.getInt(6));
                check.setMinuto(cursor.getInt(7));

                if (cursor.getInt(8) == CHECKIN) {
                    check.setCheckIn(true);
                } else {
                    check.setCheckIn(false);
                }

                list.add(check);
            } while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    private static class OpenHelper extends SQLiteOpenHelper {

        private final String CREATE_DATABASE = "CREATE TABLE horas (" + "id           INTEGER," + "dia          INT             NOT NULL,"
                + "mes          INT             NOT NULL," + "ano          INT             NOT NULL," + "datastring   VARCHAR( 10 ),"
                + "milisegundos VARCHAR( 200 )  NOT NULL," + "hora         INT             NOT NULL,"
                + "minuto       INT             NOT NULL," + "tipo         INT             NOT NULL," + "PRIMARY KEY ( id COLLATE 'NOCASE' DESC ));";

        OpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);            
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_DATABASE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w("Example", "Upgrading database, this will drop tables and recreate.");
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
        }
    }
}